<?php
/**
 * Desc: pdo连接数据库
 * Auth: Shaoming Lu<lushao1012@163.com>
 * Date: 2018/6/4
 * Time: 10:08
 */
namespace App\Http\Services;

class DB
{
    private $conn;
    private $sql;
    private $host;
    private $username;
    private $password;
    private $port;
    private $connect_data;
    private $db;
    private $field;
    private $result;
    private $error;

    public function __construct($db = '')
    {
        $data = session('db');
//        var_dump($data);exit;
        $this->connect_data = $data;
        $this->host = $data['host'];
        $this->username = $data['username'];
        $this->password = $data['password'];
        $this->port = $data['port'];
        $this->db = $db;
        $this->conn = null;
        $this->sql = '';
        $this->result = null;
        $this->field = null;
        $this->error = '';

        $this->connect();
    }

    public function __destruct()
    {
        // TODO: Implement __destruct() method.
        $this->conn = null;
    }

    /**
     * 连接数据库，连接失败，直接ajax返回出错信息
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function connect()
    {
        try{
            if ($this->db) {
                $this->conn = new \PDO('mysql:host='.$this->host.';dbname='.$this->db, $this->username, $this->password, [
                    \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES'utf8';"
                ]);
            } else {
                $this->conn = new \PDO('mysql:host='.$this->host, $this->username, $this->password, [
                    \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES'utf8';"
                ]);
            }
        } catch (\PDOException $e) {
            if($_SERVER['REQUEST_METHOD'] == 'POST') {
                AjaxService::ajax_return(['error' => "Can't connect to MySQL Server. ErrorMsg:  " . $e->getMessage()]);
            } else {
                exit(redirect()->action('ErrorController@index', ['error_msg' => "Can't connect to MySQL Server. ErrorMsg:  " . $e->getMessage()]));
//                echo "Can't connect to MySQL Server. ErrorMsg:  " . $e->getMessage();
//                return redirect()->action('ErrorController@index', ['error_msg' => "Can't connect to MySQL Server. ErrorMsg:  " . $e->getMessage()]);
            }
            exit;
        }

    }

    /**
     * 获取错误信息
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return string
     */
    public function get_error()
    {
        return $this->error;
    }

    /**
     * 返回最后一次操作数据库的错误信息描述。该方法为私有方法
     * @access  private
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return mixed
     */
    private function get_last_error()
    {
        /**
         * $error[0]: 错误码  和  $pdo->errorCode()相同
         * $error[1]: 错误编号
         * $error[2]: 错误详细信息
        */
        $error = $this->conn->errorInfo();
        return $error[0].'. '.$error[1]. '. '.$error[2].'.<br>The SQL: '.$this->get_last_sql();
    }

    /**
     * 获取当前实例最后一次执行的SQL语句
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return string
     */
    public function get_last_sql()
    {
        return $this->sql;
    }

    /**
     * SQL预处理
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param   $sql
     * @return  mixed
     */
    public function prepare_sql($sql)
    {
        return $this->conn->prepare($sql);
    }

    /**
     * 执行一条 SQL 语句，并返回受影响的行数，不适用SELECT查询
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param string $sql
     * @return mixed
     */
    public function execute($sql = '')
    {
        $this->sql = $sql;
        $stmt = $this->prepare_sql($sql);
        $count = $stmt->execute();
        if ($count === false) {
            $this->error = $this->get_last_error();
            return false;
        } else {
            return $count;
        }
    }

    /**
     * 获取查询字段信息
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $sql
     * @return null
     */
    public function get_fields($sql)
    {
        $stmt = $this->prepare_sql($sql);
        $stmt->execute();
        $this->field = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        return $this->field;
    }

    /**
     * SQL查询
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param string $sql
     * @return bool
     */
    public function query($sql = '')
    {
        $this->sql = $sql;
        $list = $this->conn->query($sql);
        if ($list === FALSE) {
            $this->error = $this->get_last_error();
            return false;
        }
        $temp = [];
        foreach ($list as $row) {
            $temp[] = $row;
        }
        $this->result = $temp;
        return $temp;
    }

    public function query_result_list($sql)
    {
        $this->sql = $sql;
        $list = $this->conn->query($sql);
        if ($list === FALSE) {
            $this->error = $this->get_last_error();
            return false;
        }
        $temp = [];
        foreach ($list as $row) {
            $temp[] = $row;
        }
        $field = [];
        for ($i = 0; $i < $list->columnCount(); $i++) {
            $field[] = $list->getColumnMeta($i)['name'];
        }
        $this->field = $field;
        $this->result = $temp;

        $ret['field'] = $field;
        $ret['result'] = $temp;
        return $ret;
    }

    /**
     * 获取所有数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return bool
     */
    public function show_all_databases()
    {
        return $this->query('SHOW DATABASES');
    }

    /**
     * 获取所有表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @return bool
     */
    public function show_all_tables()
    {
        return $this->query('SHOW TABLES');
    }

    /**
     * 获取所有表及注释
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $db_name
     * @return bool
     */
    public function get_all_tables_and_comment($db_name)
    {
        return $this->query('SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema="'.$db_name.'";');
    }

    public function show_all_charset()
    {
        return $this->query('SHOW CHARACTER SET');
    }

    /**
     * 获取字段名和注释
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @return bool
     */
    public function get_fields_list($table)
    {
        $sql = "SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='".$table."' AND table_schema='".$this->db."'";
        return $this->query($sql);
    }

    /**
     * 获取字段详情
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table    表名
     * @param $field    字段名
     * @return bool
     */
    public function get_fields_data($table, $field)
    {
        $sql = "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='".$table."' AND TABLE_SCHEMA='".$this->db."' AND COLUMN_NAME='".$field."'";
        return $this->query($sql);
    }

    /**
     * 查询列表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param string $field
     * @param string $order
     * @param int $page
     * @param int $limit
     * @return bool
     */
    public function get_list($table, $field = '*', $order = '', $page = 1, $limit = 25)
    {
        if (is_array($field)) {
            $field = implode(',', $field);
        }
        $order_by = '';
        if ($order) {
            $order_by = "ORDER BY ".$order;
        }
        $sql  = 'SELECT '.$field.' FROM '.$table.' '.$order_by.' LIMIT '.(($page-1)*$limit).','.$limit;
        $ret = $this->query_result_list($sql);;
//        var_dump($ret);exit;
        return $ret;
    }

    /**
     * 获取总数
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @return int
     */
    public function get_total_rows($table)
    {
        $sql  = 'SELECT count(*) as total FROM '.$table;
        $total = $this->query($sql);
        return (isset($total[0]['total']) && $total[0]['total']) ? $total[0]['total'] : 0;
    }

    /**
     * 获取表结构
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @return bool
     */
    public function get_table_structure($table)
    {
        $this->query('USE information_schema;');
        $res = $this->query('SELECT * FROM COLUMNS WHERE table_schema=\''.$this->db.'\' AND TABLE_NAME=\''.$table.'\'');
        return $res;
    }

    /**
     * 删除表字段
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param $field
     */
    public function drop_table_field($table, $field)
    {
        $this->query('ALTER TABLE '.$table.' DROP '.$field);
    }

    /**
     * 修改表字段
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $table
     * @param $field
     * @param $str
     */
    public function alter_table_field($table, $field, $str)
    {
        $this->query('ALTER TABLE '.$table.' CHANGE '.$field.' '.$str);
    }

    /**
     * 增加表字段
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $table    表名
     * @param $after    添加在哪个字段后面
     * @param $str      字段名
     */
    public function add_table_field($table, $after, $str)
    {
        $this->query('ALTER TABLE '.$table.' ADD '.$str.' AFTER '.$after);
    }

    /**
     * 获取表信息
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $table    表名
     * @return array
     */
    public function get_table_info($table)
    {
        if (empty($table)) {
            return [];
        }
        $list = $this->query('SELECT * FROM information_schema.tables WHERE table_schema="'. $this->db .'" and table_name="'. $table .'"');
        return $list ? $list[0] : [];
    }

    /**
     * 根据ID查询数据
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param string         $table 表名
     * @param string|integer $id    主键ID
     * @return bool|array
     */
    public function get_data_by_id($table, $id)
    {
        $sql = "SELECT * FROM {$table} WHERE id='{$id}'";
        $data = $this->query_result_list($sql);
        $data['result'] = $data['result'] ? $data['result'][0] : [];
        return $data;
    }
}